package com.hgkj.cloud.customer.excel;

import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

public class NewUserExcelParseTool {
    private String mFilePath;
    public List<String> frameworkNames = new ArrayList<String>();
    List<String> mlist;
    public List<UserExcel> mUserExcelList = new ArrayList<UserExcel>();
    public boolean invalid_method = false;  //excel第一行不对
    public String errorMsg = "";

    public NewUserExcelParseTool() {
    }

    public void setFilePath(String filePath, List<String> list) {
        mFilePath = filePath;
        mlist = list;
    }

    private static final String SUFFIX_2003 = ".xls";
    private static final String SUFFIX_2007 = ".xlsx";

    public Workbook initWorkBook() throws IOException {


        InputStream is = null;
        if (mFilePath.contains("http://")){
            URL url  = new URL(mFilePath);
            is = url.openStream();
        }else {
            File file = new File(mFilePath);
            is = new FileInputStream(file);
        }
        Workbook workbook = null;
        try {
            if (mFilePath.endsWith(SUFFIX_2003)) {
                workbook = new HSSFWorkbook(is);
            } else if (mFilePath.endsWith(SUFFIX_2007)) {
                workbook = new XSSFWorkbook(is);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(is);
        }

        return workbook;
    }

    public void parseWorkbook(Workbook workbook) {
        int numOfSheet = workbook.getNumberOfSheets();

        for (int i = 0; i < numOfSheet; ++i) {
            Sheet sheet = workbook.getSheetAt(i);
            parseSheet(sheet);
        }
    }

    private void parseSheet(Sheet sheet) {
        Row row;

        int count = 0;

        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()) {
            row = iterator.next();
            if (count == 0) {
                parseRowAndFindMethod(row);
                if (invalid_method) {
                    return;
                }
            } else {
                parseRowAndFillData(row, count);
                if (!StringUtils.isEmpty(errorMsg)) {
                    return;
                }
            }
            ++count;

        }
    }

    /**
     * 判断第一行是否正确
     *
     * @param row
     */
    private void parseRowAndFindMethod(Row row) {
        List<String> rst = parseRow(row);
        if (rst.size() != mlist.size()) {
            this.invalid_method = true;
        }
        for (int i = 0; i < rst.size(); i++) {
            if (i == 0 && !mlist.get(i).equals(rst.get(i))) {
                this.invalid_method = true;
            }
        }
    }

    private void parseRowAndFillData(Row row, int count) {
        List<String> rst = parseRow(row);

        try {
            UserExcel qnexcel = new UserExcel();
            List<String> list = new ArrayList<>();
            for (int i = 0; i < rst.size(); i++) {
                if (rst.get(0) != null && !"".equals(rst.get(0))) {
                    String str = rst.get(i);
                    str.trim();
                    if (i == 0) {
                        if (StringUtils.isEmpty(str)) {
                            errorMsg = "第" + (count + 1) + "行，用户名为空。";
                            return;
                        }
                        qnexcel.setUser_name(str);
                    } else if (i == 1) {
                        if (StringUtils.isEmpty(str)) {
                            errorMsg = "第" + (count + 1) + "行，姓名为空。";
                            return;
                        }
                        qnexcel.setReal_name(str);
                    } else if (i == 2) {
                        if (StringUtils.isEmpty(str)) {
                            errorMsg = "第" + (count + 1) + "行，性别为空。";
                            return;
                        }
                        qnexcel.setGender(str);
                    } else if (i == 3) {
                        if (StringUtils.isEmpty(str)) {
                            errorMsg = "第" + (count + 1) + "行，出生日期为空。";
                            return;
                        }
                        String doubleToDate = ExcelDoubleToDate(str);
                        if (str.equals(doubleToDate)) {
                            errorMsg = "第" + (count + 1) + "行，出生日期格式不正确。支持格式为XXXX年XX月XX日 或者XXXX/XX/XX。";
                            return;
                        }
                        qnexcel.setUser_birth(doubleToDate);
                    } else if (i == 4) {
                        if (StringUtils.isEmpty(str)) {
                            errorMsg = "第" + (count + 1) + "行，组织机构为空。";
                            return;
                        }
                        if (!frameworkNames.contains(str)) {
                            frameworkNames.add(str);
                        }
                        qnexcel.setUser_framework(str);
                    } else {
                        list.add(str == null ? "" : str);
                    }
                }
            }
            if (rst.get(0) != null && !"".equals(rst.get(0))) {
                qnexcel.setList(list);
                mUserExcelList.add(qnexcel);
            }

        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }

    public static String ExcelDoubleToDate(String strDate) {
        if (strDate.length() == 5) {
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Date tDate = DoubleToDate(Double.parseDouble(strDate));
                return sdf.format(tDate);
            } catch (Exception e) {
                e.printStackTrace();
                return strDate;
            }
        }
        return strDate;
    }

    //解析Excel日期格式
    public static Date DoubleToDate(Double dVal) {
        Date tDate = new Date();
        long localOffset = tDate.getTimezoneOffset() * 60000; //系统时区偏移 1900/1/1 到 1970/1/1 的 25569 天
        tDate.setTime((long) ((dVal - 25569) * 24 * 3600 * 1000 + localOffset));

        return tDate;
    }

    private List<String> parseRow(Row row) {
        List<String> rst = new ArrayList<>();
        Cell cell;
        Iterator<Cell> iterator = row.iterator();
        while (iterator.hasNext()) {
            cell = iterator.next();
            cell.setCellType(CellType.STRING);
            rst.add(cell.getStringCellValue());
        }
        return rst;
    }
}
